/******************************************************************************/
/****         Generated by IBExpert 2007.12.01 09/01/2009 12:37:02         ****/
/******************************************************************************/



/******************************************************************************/
/****                              Generators                              ****/
/******************************************************************************/

CREATE GENERATOR GEN_CAMION_ID;
SET GENERATOR GEN_CAMION_ID TO 0;

CREATE GENERATOR GEN_CLIENT_ID;
SET GENERATOR GEN_CLIENT_ID TO 0;

CREATE GENERATOR GEN_LOCATION_ID;
SET GENERATOR GEN_LOCATION_ID TO 0;

CREATE GENERATOR GEN_MATERIEL_ID;
SET GENERATOR GEN_MATERIEL_ID TO 0;

CREATE GENERATOR GEN_PELLETEUSE_ID;
SET GENERATOR GEN_PELLETEUSE_ID TO 0;

CREATE GENERATOR GEN_TONDEUSE_ID;
SET GENERATOR GEN_TONDEUSE_ID TO 0;



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE CAMION (
    CODE_CAMION         INTEGER NOT NULL,
    POIDSTOTAL          NUMERIC(15,2),
    POIDSTRANSPORTABLE  NUMERIC(15,2),
    CODE_MATERIEL       INTEGER
);

CREATE TABLE CLIENT (
    CODE_CLIENT  INTEGER NOT NULL,
    NOM          VARCHAR(50),
    PRENOM       VARCHAR(50),
    AGE          INTEGER
);

CREATE TABLE LOCATION (
    CODE_LOCATION    INTEGER NOT NULL,
    CODE_CLIENT      INTEGER,
    CODE_MATERIEL    INTEGER,
    DEBUT_LOCATION   TIMESTAMP,
    RETOUR_LOCATION  TIMESTAMP
);

CREATE TABLE MATERIEL (
    CODE_MATERIEL  INTEGER NOT NULL,
    NOM            VARCHAR(50),
    DESCRIPTION    BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    PRIX           NUMERIC(15,2),
    TYPE_MATERIEL  INTEGER
);

CREATE TABLE PELLETEUSE (
    CODE_PELLETEUSE  INTEGER NOT NULL,
    POIDSTOTAL       DECIMAL(15,2),
    CODE_MATERIEL    INTEGER
);

CREATE TABLE TONDEUSE (
    CODE_TONDEUSE  INTEGER NOT NULL,
    VITESSE        NUMERIC(15,2),
    CODE_MATERIEL  INTEGER
);



/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE CAMION ADD CONSTRAINT PK_CAMION PRIMARY KEY (CODE_CAMION);
ALTER TABLE CLIENT ADD CONSTRAINT PK_CLIENT PRIMARY KEY (CODE_CLIENT);
ALTER TABLE LOCATION ADD CONSTRAINT PK_LOCATION PRIMARY KEY (CODE_LOCATION);
ALTER TABLE MATERIEL ADD CONSTRAINT PK_MATERIEL PRIMARY KEY (CODE_MATERIEL);
ALTER TABLE PELLETEUSE ADD CONSTRAINT PK_PELLETEUSE PRIMARY KEY (CODE_PELLETEUSE);
ALTER TABLE TONDEUSE ADD CONSTRAINT PK_TONDEUSE PRIMARY KEY (CODE_TONDEUSE);


/******************************************************************************/
/****                             Foreign Keys                             ****/
/******************************************************************************/

ALTER TABLE CAMION ADD CONSTRAINT FK_CAMION_1 FOREIGN KEY (CODE_MATERIEL) REFERENCES MATERIEL (CODE_MATERIEL) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE LOCATION ADD CONSTRAINT FK_LOCATION_1 FOREIGN KEY (CODE_CLIENT) REFERENCES CLIENT (CODE_CLIENT) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE LOCATION ADD CONSTRAINT FK_LOCATION_2 FOREIGN KEY (CODE_MATERIEL) REFERENCES MATERIEL (CODE_MATERIEL) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE PELLETEUSE ADD CONSTRAINT FK_PELLETEUSE_1 FOREIGN KEY (CODE_MATERIEL) REFERENCES MATERIEL (CODE_MATERIEL) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE TONDEUSE ADD CONSTRAINT FK_TONDEUSE_1 FOREIGN KEY (CODE_MATERIEL) REFERENCES MATERIEL (CODE_MATERIEL) ON DELETE CASCADE ON UPDATE CASCADE;


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE UNIQUE INDEX CLIENT_IDX1 ON CLIENT (NOM, PRENOM);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/


SET TERM ^ ;


/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: CAMION_BI */
CREATE TRIGGER CAMION_BI FOR CAMION
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.code_camion is null) then
    new.code_camion = gen_id(gen_camion_id,1);
end
^

/* Trigger: CLIENT_BI */
CREATE TRIGGER CLIENT_BI FOR CLIENT
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.code_client is null) then
    new.code_client = gen_id(gen_client_id,1);
end
^

/* Trigger: LOCATION_BI */
CREATE TRIGGER LOCATION_BI FOR LOCATION
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.code_location is null) then
    new.code_location = gen_id(gen_location_id,1);
end
^

/* Trigger: MATERIEL_BI */
CREATE TRIGGER MATERIEL_BI FOR MATERIEL
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.code_materiel is null) then
    new.code_materiel = gen_id(gen_materiel_id,1);
end
^

/* Trigger: PELLETEUSE_BI */
CREATE TRIGGER PELLETEUSE_BI FOR PELLETEUSE
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.code_pelleteuse is null) then
    new.code_pelleteuse = gen_id(gen_pelleteuse_id,1);
end
^

/* Trigger: TONDEUSE_BI */
CREATE TRIGGER TONDEUSE_BI FOR TONDEUSE
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.code_tondeuse is null) then
    new.code_tondeuse = gen_id(gen_tondeuse_id,1);
end
^

SET TERM ; ^

